﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Financeiro.Dados
{
    public class CotaDAO
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["ERP_FinanceiroConnectionString"].ConnectionString;

        public static float insertValor(string setor, double valor, char tipo)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlIns = "INSERT INTO Cota (setor, valor, tipo) VALUES (@setor, @valor, @tipo)";
            int insertID = 0;
            try
            {
                conn.Open();
                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.Add("@setor",SqlDbType.VarChar);
                cmdIns.Parameters.Add("@valor",SqlDbType.Float);
                cmdIns.Parameters.Add("@tipo",SqlDbType.Char);
                cmdIns.Parameters["@setor"].Value = setor;
                cmdIns.Parameters["@valor"].Value = valor;
                cmdIns.Parameters["@tipo"].Value = tipo;
                cmdIns.ExecuteNonQuery();

                cmdIns.Parameters.Clear();
                cmdIns.CommandText = "SELECT @@IDENTITY";

                // Get the last inserted id.
                insertID = Convert.ToInt32(cmdIns.ExecuteScalar());

                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return insertID;
        }

        public static DataRow obterUltimaCota(string setor)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlSel = "SELECT * FROM Cota WHERE (setor=@setor) ORDER BY created DESC";
            DataRow dr = null;
            try
            {
                conn.Open();
                SqlCommand cmdSel = new SqlCommand(sqlSel, conn);
                cmdSel.Parameters.Add("@setor", SqlDbType.VarChar);
                cmdSel.Parameters["@setor"].Value = setor;

                SqlDataAdapter da = new SqlDataAdapter(cmdSel);
                DataSet ds = new DataSet();
                da.Fill(ds, "Cota");

                DataTable dt = ds.Tables["Cota"];
                if (dt.Rows.Count > 0)
                    dr = dt.Rows[0];

                cmdSel.Dispose();
                cmdSel = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return dr;
        }

        public static DataRow obterCota(int id)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlSel = "SELECT * FROM Cota WHERE (id=@id)";
            DataRow dr = null;
            try
            {
                conn.Open();
                SqlCommand cmdSel = new SqlCommand(sqlSel, conn);
                cmdSel.Parameters.Add("@id", SqlDbType.Int);
                cmdSel.Parameters["@id"].Value = id;

                SqlDataAdapter da = new SqlDataAdapter(cmdSel);
                DataSet ds = new DataSet();
                da.Fill(ds, "Cota");

                DataTable dt = ds.Tables["Cota"];
                if(dt.Rows.Count>0)
                    dr = dt.Rows[0];

                cmdSel.Dispose();
                cmdSel = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return dr;
        }

        public static DataTable obterCotas(DateTime start, DateTime end, string setor)
        {
            SqlConnection conn = new SqlConnection(connectionString);

            string where = "WHERE (created>=@start AND created<=@end ";
            if (!setor.Equals(""))
                where += "AND setor=@setor";
            where += ")";

            string sqlSel = "SELECT * FROM Cota " + where 
                + " ORDER BY created DESC";
            DataTable dt;
            try
            {
                conn.Open();
                SqlCommand cmdSel = new SqlCommand(sqlSel, conn);
                cmdSel.Parameters.Add("@setor", SqlDbType.VarChar);
                cmdSel.Parameters.Add("@start", SqlDbType.DateTime);
                cmdSel.Parameters.Add("@end", SqlDbType.DateTime);
                cmdSel.Parameters["@setor"].Value = setor;
                cmdSel.Parameters["@start"].Value = start;
                cmdSel.Parameters["@end"].Value = end;

                SqlDataAdapter da = new SqlDataAdapter(cmdSel);
                DataSet ds = new DataSet();
                da.Fill(ds, "Cota");
                dt = ds.Tables["Cota"];

                cmdSel.Dispose();
                cmdSel = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }


        public static bool mudarCotaExtra(int id, double extra)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlUpd = "UPDATE Cota SET extra=@extra WHERE id=@id";
            bool hasSuccess = false;
            try
            {
                conn.Open();
                SqlCommand cmdUpd = new SqlCommand(sqlUpd, conn);
                cmdUpd.Parameters.Add("@id", SqlDbType.Int);
                cmdUpd.Parameters["@id"].Value = id;
                cmdUpd.Parameters.Add("@extra", SqlDbType.Float);
                cmdUpd.Parameters["@extra"].Value = extra;

                if (cmdUpd.ExecuteNonQuery() != 0)
                    hasSuccess = true;

                cmdUpd.Dispose();
                cmdUpd = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return hasSuccess;
        }


    }
}